package com.qgxy.mysqlite;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

import com.example.sqlview.StudentBean;

import java.util.ArrayList;
import java.util.List;

public class MySQLiteOpenHelper extends SQLiteOpenHelper {

    private static final String DB_NAME = "scoreCollectDB.db";   //定义数据库名称
    private static final String TABLE_NAME_STUDENT = "student";  //定义表名称
    private static final String CREATE_TABLE_SQL_STUDENT = "create table " + TABLE_NAME_STUDENT
            + " (_id integer primary key autoincrement, name text, number text, gender text, score text)";  //创建表的SQL语句

    public MySQLiteOpenHelper(@Nullable Context context) {
        super(context, DB_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_SQL_STUDENT);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public long insertData(StudentBean student){

        SQLiteDatabase db = getWritableDatabase();

        ContentValues contentValues = new ContentValues();
        contentValues.put("name", student.getName());
        contentValues.put("number", student.getNumber());
        contentValues.put("gender", student.getGender());
        contentValues.put("score", student.getScore());
        long l = db.insert(TABLE_NAME_STUDENT, null, contentValues);
//        db.close();
        return l;
    }

    public int deleteDataFromDbByName(String name){
        SQLiteDatabase db = getWritableDatabase();
        int i = db.delete(TABLE_NAME_STUDENT, "name=?", new String[]{name});
//        db.close();
        return i;
    }

    public int updateDataByName(StudentBean student){
        SQLiteDatabase db = getWritableDatabase();

        ContentValues contentValues = new ContentValues();
        contentValues.put("number", student.getNumber());
        contentValues.put("gender", student.getGender());
        contentValues.put("score", student.getScore());

        int i = db.update(TABLE_NAME_STUDENT, contentValues, "name=?", new String[]{student.getName()});
//        db.close();
        return i;
    }

    public List<StudentBean> queryFromDbByName(String name){

        List<StudentBean> studentBeanList = new ArrayList<>();

        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(TABLE_NAME_STUDENT, null, "name=?", new String[]{name}, null, null, null);

        if (cursor != null){
            while(cursor.moveToNext()){
                String str_name = cursor.getString(cursor.getColumnIndex("name"));
                String str_number = cursor.getString(cursor.getColumnIndex("number"));
                String str_gender = cursor.getString(cursor.getColumnIndex("gender"));
                String str_score = cursor.getString(cursor.getColumnIndex("score"));
                StudentBean student = new StudentBean();
                student.setName(str_name);
                student.setNumber(str_number);
                student.setGender(str_gender);
                student.setScore(str_score);
                studentBeanList.add(student);
            }
            cursor.close();
        }
//        db.close();
        return studentBeanList;
    }
}
